"""
Case Type   : 功能测试
Case Name   : 物化视图主备同步验证
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1.创建表并插入数据
    2.创建物化视图
    3.主、备节点上查询数据
    4.再插入数据，并创建增量物化视图
    5.主、备节点上查询数据
    6.refresh 全量物化视图
    7.主、备节点上查询数据
Expect      :
    1.成功
    2.成功
    3.主备数据一致
    4.成功
    5.主备数据一致,增量物化视图数据比全量物化视图多两行
    6.成功
    7.主备数据一致
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common


class PageTableTestCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby_sh = CommonSH('Standby1DbUser')
        self.user_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_page_table_test061'
        self.materialized_view_name = 'mv_page_table_test061'
        self.incremental_view_name = 'iv_page_table_test061'
        self.backup_path = os.path.join(macro.DB_BACKUP_PATH,
                                        'MPPDB_backup.sql')

    def test_page_table(self):
        step = '-----step1:创建表并插入数据;expect:成功-----'
        self.logger.info(step)
        sql_cmd = f"drop table if exists {self.table_name};" \
                  f"create table {self.table_name}(t1 int, t2 text);" \
                  f"insert into {self.table_name} values (1, '第一行');" \
                  f"insert into {self.table_name} values (2, '第二行');"
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg,
                      '执行失败:' + step)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + step)

        step = '-----step2:创建物化视图 expect:成功-----'
        self.logger.info(step)
        sql_cmd = f"create materialized view {self.materialized_view_name} " \
                  f"as select * from {self.table_name};"
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertIn("CREATE MATERIALIZED VIEW", msg, '执行失败:' + step)

        step = '-----step3:主、备节点上查询数据;expect:主备数据一致-----'
        self.logger.info(step)
        sql_cmd = f"select * from {self.materialized_view_name};"
        pri_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(pri_msg)
        sta_msg = self.standby_sh.execut_db_sql(sql_cmd)
        self.logger.info(sta_msg)
        self.assertEqual(pri_msg, sta_msg, '执行失败:' + step)

        step = '-----step4:再插入数据，并创建增量物化视图;expect:成功-----'
        self.logger.info(step)
        sql_cmd = f"insert into {self.table_name} values (3, '第三行');" \
                  f"insert into {self.table_name} values (4, '第四行');" \
                  f"create incremental materialized view " \
                  f"{self.incremental_view_name} as select * from " \
                  f"{self.table_name};"
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + step)
        self.assertIn("CREATE MATERIALIZED VIEW", msg, '执行失败:' + step)

        step = '----step5:主、备节点上查询数据 expect:主备数据一致,' \
               '增量物化视图数据比全量物化视图多两行----'
        self.logger.info(step)
        sql_cmd = f"select * from {self.materialized_view_name};"
        self.logger.info(sql_cmd)
        pri_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(pri_msg)
        sta_msg = self.standby_sh.execut_db_sql(sql_cmd)
        self.logger.info(sta_msg)
        self.assertEqual(pri_msg, sta_msg, '执行失败:' + step)
        self.assertIn("2 rows", pri_msg, '执行失败:' + step)
        sql_cmd = f"select * from {self.incremental_view_name};"
        self.logger.info(sql_cmd)
        pri_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(pri_msg)
        sta_msg = self.standby_sh.execut_db_sql(sql_cmd)
        self.logger.info(sta_msg)
        self.assertEqual(pri_msg, sta_msg, '执行失败:' + step)
        self.assertIn("4 rows", pri_msg, '执行失败:' + step)

        step = '-----step6:refresh 全量物化视图;expect:成功-----'
        self.logger.info(step)
        sql_cmd = f"refresh materialized view {self.materialized_view_name};"
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertEqual("REFRESH MATERIALIZED VIEW", msg, '执行失败:' + step)

        step = '-----step7:主、备节点上查询数据 expect:主备数据一致-----'
        self.logger.info(step)
        sql_cmd = f"select * from {self.materialized_view_name};"
        pri_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(pri_msg)
        sta_msg = self.standby_sh.execut_db_sql(sql_cmd)
        self.logger.info(sta_msg)
        self.assertEqual(pri_msg, sta_msg, '执行失败:' + step)
        self.assertIn("4 rows", pri_msg, '执行失败:' + step)

    def tearDown(self):
        step = '----清理环境----'
        self.logger.info(step)
        sql_cmd = f"drop table {self.table_name} cascade;"
        drop_msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(drop_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_msg,
                         '执行失败:' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')

